const nodeXlsx = require('node-xlsx'); //引用node-xlsx模块
const oracleTool = require('../../oracle'); // 引用oracle

const excelPath = '../../data/tms_gree_bl_data.xlsx';

executeSQL('GREE', 'GREE0000000000000000', '000000000000');

async function executeSQL(grpCode, entCode, brnNbr) {
    console.log('导入保理应付账款 -- 开始');
    let start = Date.now();

    //查询意向申请
    let sql = 'select INT_NO,CLT_NBR from FMDBRUN.LON_FACT_INT where GRP_CODE = :grpCode and ENT_CODE = :entCode';
    let params = {
        grpCode: {dir: oracleTool.BIND_IN, type: oracleTool.STRING, val: grpCode},
        entCode: {dir: oracleTool.BIND_IN, type: oracleTool.STRING, val: entCode}
    };
    let factIntResult = await oracleTool.queryByParams(sql, params, false);
    let factIntList = handleOracleData(factIntResult);

    //查询意向申请业绩信息
    sql = 'select INT_NO,AGENCY_NBR,AGENCY_NAME from FMDBRUN.LON_FACT_PER_INFO where GRP_CODE = :grpCode and ENT_CODE = :entCode';
    let factPerInfoResult = await oracleTool.queryByParams(sql, params, false);
    let factPerInfoList = handleOracleData(factPerInfoResult);
    let lonFactInfoCtDTOMap = new Map();
    factPerInfoList.forEach(x => {
        lonFactInfoCtDTOMap.set(x.INT_NO, x);
    })
    let sellNbrFactInfoMap = new Map();
    factIntList.forEach(x => {
        sellNbrFactInfoMap.set(x.CLT_NBR, lonFactInfoCtDTOMap.get(x.INT_NO));
    })

    //应付账款数据
    let payableFundList = getPayableFundExcelData();
    let fundMap = new Map();
    payableFundList.forEach(x => {
        fundMap.set(convertCltName(x.SELL_CLT_NAME), x);
    })

    //账款买卖方未结算金额详细数据
    let payableFundDetailList = getPayableFundDetailExcelData();
    if (payableFundDetailList.length === 0) {
        console.log('没有获取到excel中的应付账款数据');
        return;
    }
    //以卖方统计买卖方未结算金额详细数据，便于与汇总数据对比正确性
    let sellDetailSumMap = new Map();
    payableFundDetailList.forEach(x => {
        let cltName = convertCltName(x.SELL_CLT_NAME);
        if (!sellDetailSumMap.get(cltName)) {
            let item = {UNBALANCE_USED: x.UNBALANCE_USED, UNBALANCE_IN_STORAGE: x.UNBALANCE_IN_STORAGE}
            sellDetailSumMap.set(cltName, item);
        } else {
            let item = sellDetailSumMap.get(cltName);
            item.UNBALANCE_USED = item.UNBALANCE_USED + x.UNBALANCE_USED;
            item.UNBALANCE_IN_STORAGE = item.UNBALANCE_IN_STORAGE + x.UNBALANCE_IN_STORAGE;
            sellDetailSumMap.set(cltName, item);
        }
    })

    //校验买卖方详细数据卖方汇总的金额与汇总数据对应
    fundMap.forEach((value, key) => {
        let item = sellDetailSumMap.get(key);
        if (item.UNBALANCE_USED !== value.UNBALANCE_USED) {
            console.log('卖方=[%s]的未结算已使用汇总金额与明细不一致', key)
        }
        if (item.UNBALANCE_IN_STORAGE !== value.UNBALANCE_IN_STORAGE) {
            console.log('卖方=[%s]的未结算已入库汇总金额与明细不一致', key)
        }
    })

    let date = new Date();
    payableFundDetailList.forEach(x => {
        let fundEntity = fundMap.get(convertCltName(x.SELL_CLT_NAME));
        x.GRP_CODE = grpCode;
        x.BRN_NBR = brnNbr;
        x.FUND_NBR = getUUID();
        x.YEAR_MONTH = fundEntity.YEAR_MONTH;
        if (fundEntity.BUY_CLT_NBR.substring(fundEntity.BUY_CLT_NBR.length - 1, fundEntity.BUY_CLT_NBR.length) === "、") {
            fundEntity.BUY_CLT_NBR = fundEntity.BUY_CLT_NBR.substring(0, fundEntity.BUY_CLT_NBR.length - 1);
        }
        if (fundEntity.BUY_CLT_NAME.substring(fundEntity.BUY_CLT_NAME.length - 1, fundEntity.BUY_CLT_NAME.length) === "、") {
            fundEntity.BUY_CLT_NAME = fundEntity.BUY_CLT_NAME.substring(0, fundEntity.BUY_CLT_NAME.length - 1);
        }
        let buyNbrs = fundEntity.BUY_CLT_NBR.split("、");
        let buyNames = fundEntity.BUY_CLT_NAME.split("、");
        for (let i = 0; i < buyNames.length; i++) {
            if (convertCltName(buyNames[i]) === convertCltName(x.BUY_CLT_NAME)) {
                x.BUY_CLT_NBR = buyNbrs[i];
                break;
            }
        }
        x.SELL_CLT_NBR = fundEntity.SELL_CLT_NBR;
        x.REMARK = fundEntity.REMARK;
        x.CCY_NBR = transferCcyName(fundEntity.CCY_NBR);
        x.CONFIRM_CODE = '1';
        x.STS_CODE = '1';
        x.SUPPLIER_CODE = fundEntity.SUPPLIER_CODE;
        x.CREATE_TIME = date;
        x.LST_TIME = date;
        let lonFactInfoCtDTO = sellNbrFactInfoMap.get(x.SELL_CLT_NBR);
        x.AGENCY_NBR = lonFactInfoCtDTO.AGENCY_NBR;
        x.AGENCY_NAME = lonFactInfoCtDTO.AGENCY_NAME;
    })

    //有代理买方的生成在代理买方下，没有的生成在买方下
    let cltNbrsParam = '';
    payableFundDetailList.forEach(x => {
        let cltNbr;
        if (x.AGENCY_NBR) {
            cltNbr = x.AGENCY_NBR;
        } else {
            cltNbr = x.BUY_CLT_NBR
        }
        if (cltNbrsParam.indexOf(cltNbr) === -1) {
            cltNbrsParam += '\'' + cltNbr + '\',';
        }
    })

    //查询所有客户号的企业信息
    cltNbrsParam = cltNbrsParam.substring(0, cltNbrsParam.length - 1);
    sql = 'SELECT CLT_NBR,CLT_ENT_CODE FROM FMDBRUN.CVM_CUSTOM where GRP_CODE = :grpCode and CLT_NBR in (' + cltNbrsParam + ')';
    params = {grpCode: {dir: oracleTool.BIND_IN, type: oracleTool.STRING, val: grpCode}};
    let cvmCustomDTOS = await oracleTool.queryByParams(sql, params, false);
    cvmCustomDTOS = handleOracleData(cvmCustomDTOS);
    let cvmCustomDTOMap = new Map();
    cvmCustomDTOS.forEach(x => {
        cvmCustomDTOMap.set(x.CLT_NBR, x);
    })

    //关联客户信息，查询客户号对应的企业号
    let entityList = [];
    for (let i = 0; i < payableFundDetailList.length; i++) {
        let entity = payableFundDetailList[i];
        let cltNbr = entity.AGENCY_NBR ? entity.AGENCY_NBR : entity.BUY_CLT_NBR;
        let cvmCustomDTO = cvmCustomDTOMap.get(cltNbr);
        if (!cvmCustomDTO) {
            console.log('导入保理应付账款 -- 没有找到CLT_NBR=%s的客户信息', cltNbr);
            continue;
        }
        if (!cvmCustomDTO.CLT_ENT_CODE) {
            console.log('导入保理应付账款 -- CLT_NBR=%s的客户信息中CLT_ENT_CODE为空', cltNbr);
            continue;
        }
        entity.ENT_CODE = cvmCustomDTO.CLT_ENT_CODE;
        entityList.push(entity);
    }
    if (entityList.length === 0) {
        console.log('没有有效的应付账款数据');
        return;
    }

    //删除已存在的账款数据
    let ENT_CODES = '';
    let YEAR_MONTHS = '';
    let BUY_CLT_NBRS = '';
    let SELL_CLT_NBRS = '';
    entityList.forEach(x => {
        if (ENT_CODES.indexOf(x.ENT_CODE) === -1) {
            ENT_CODES += '\'' + x.ENT_CODE + '\',';
        }
        if (YEAR_MONTHS.indexOf(x.YEAR_MONTH) === -1) {
            YEAR_MONTHS += '\'' + x.YEAR_MONTH + '\',';
        }
        if (BUY_CLT_NBRS.indexOf(x.BUY_CLT_NBR) === -1) {
            BUY_CLT_NBRS += '\'' + x.BUY_CLT_NBR + '\',';
        }
        if (SELL_CLT_NBRS.indexOf(x.SELL_CLT_NBR) === -1) {
            SELL_CLT_NBRS += '\'' + x.SELL_CLT_NBR + '\',';
        }
    })
    ENT_CODES = ENT_CODES.substring(0, ENT_CODES.length - 1);
    YEAR_MONTHS = YEAR_MONTHS.substring(0, YEAR_MONTHS.length - 1);
    BUY_CLT_NBRS = BUY_CLT_NBRS.substring(0, BUY_CLT_NBRS.length - 1);
    SELL_CLT_NBRS = SELL_CLT_NBRS.substring(0, SELL_CLT_NBRS.length - 1);
    sql = 'delete from FMDBRUN.NET_PAYABLE_FUND where GRP_CODE = \'' + grpCode + '\' and ENT_CODE in (' + ENT_CODES + ') and BUY_CLT_NBR in (' + BUY_CLT_NBRS + ') and SELL_CLT_NBR in (' + SELL_CLT_NBRS + ')';
    let result = await oracleTool.execute(sql, {});
    if (result.state) {
        console.log('导入保理应付账款 -- 删除历史年月买卖方数据');
    }

    //批量插入应付账款信息
    sql = 'insert into FMDBRUN.NET_PAYABLE_FUND(GRP_CODE,ENT_CODE,BRN_NBR,FUND_NBR,YEAR_MONTH,BUY_CLT_NBR,BUY_CLT_NAME,SELL_CLT_NBR,SELL_CLT_NAME,UNBALANCE_USED,UNBALANCE_IN_STORAGE,TOTAL_AMT,REMARK,CCY_NBR,CONFIRM_CODE,STS_CODE,SUPPLIER_CODE,AGENCY_NBR,AGENCY_NAME,CREATE_TIME,LST_TIME) values ' + '(:GRP_CODE,:ENT_CODE,:BRN_NBR,:FUND_NBR,:YEAR_MONTH,:BUY_CLT_NBR,:BUY_CLT_NAME,:SELL_CLT_NBR,:SELL_CLT_NAME,:UNBALANCE_USED,:UNBALANCE_IN_STORAGE,:TOTAL_AMT,:REMARK,:CCY_NBR,:CONFIRM_CODE,:STS_CODE,:SUPPLIER_CODE,:AGENCY_NBR,:AGENCY_NAME,:CREATE_TIME,:LST_TIME)';
    result = await oracleTool.executeMany(sql, entityList, {autoCommit: true});
    let end = Date.now();
    if (result.state) {
        console.log('导入保理应付账款 -- 成功=%s条，耗时=%s秒', entityList.length, (end - start) / 1000);
    } else {
        console.log('导入保理应付账款 -- 失败：%s', JSON.stringify(result));
    }
}

//处理oracle数据库查询list数据
function handleOracleData(result) {
    //属性
    let metaData = [];
    result.data.metaData.forEach(x => {
        metaData.push(x.name);
    })
    let list = [];
    result.data.rows.forEach(x => {
        let entity = {};
        for (let i = 0; i < metaData.length; i++) {
            entity[metaData[i]] = x[i];
        }
        list.push(entity);
    })
    return list;
}

//读取应付账款excel数据
function getPayableFundExcelData() {
    //取出excel中的应付账款
    let excelData = getSheetByPathAndSheetName(excelPath, '应付账款');
    //去掉标题
    excelData.splice(0, 1);
    //返回结果
    let result = [];
    for (let i = 0; i < excelData.length; i++) {
        let excelItem = excelData[i];
        let item = {};
        if (excelItem == null || excelItem.length === 0) {
            continue;
        }
        item.YEAR_MONTH = excelItem[3];
        item.BUY_CLT_NBR = excelItem[4] ? excelItem[4].toString() : excelItem[4];
        item.BUY_CLT_NAME = excelItem[5];
        item.SELL_CLT_NBR = excelItem[6] ? excelItem[6].toString() : excelItem[6];
        item.SELL_CLT_NAME = excelItem[7];
        item.UNBALANCE_USED = excelItem[8];
        item.UNBALANCE_IN_STORAGE = excelItem[9];
        item.TOTAL_AMT = excelItem[10];
        item.REMARK = excelItem[11];
        item.CCY_NBR = excelItem[13];
        item.SUPPLIER_CODE = excelItem[16] ? excelItem[16].toString() : excelItem[16];
        result.push(item);
    }
    return result;
}

//读取应付账款买卖方未结算金额明细excel数据
function getPayableFundDetailExcelData() {
    //取出excel中的应付账款买卖方明细
    let excelData = getSheetByPathAndSheetName(excelPath, '应付账款买卖方明细');
    //去掉标题
    excelData.splice(0, 1);
    //返回结果
    let result = [];
    let row = excelData.length;
    for (let i = 0; i < row; i++) {
        let excelItem = excelData[i];
        let item = {};
        if (excelItem == null || excelItem.length === 0) {
            continue;
        }
        item.SELL_CLT_NAME = excelItem[0];
        item.BUY_CLT_NAME = excelItem[1];
        item.UNBALANCE_USED = excelItem[2];
        item.UNBALANCE_IN_STORAGE = excelItem[3];
        item.TOTAL_AMT = excelItem[4];
        result.push(item);
    }
    return result;
}

//通过excelPath获取指定sheetName的sheet数据
function getSheetByPathAndSheetName(excelPath, sheetName) {
    // 解析表格
    let sheet = nodeXlsx.parse(excelPath);
    //获取sheet.data
    for (let i = 0; i < sheet.length; i++) {
        if (sheet[i].name === sheetName) {
            return sheet[i].data;
        }
    }
    return [];
}

function convertCltName(name) {
    if (name.indexOf("(") !== -1) {
        name = name.replace(/\(/g, "（");
    }
    if (name.indexOf(")") !== -1) {
        name = name.replace(/\)/g, "）");
    }
    return name.trim();
}

function transferCcyName(name) {
    if (!name) return null;
    name = name.trim();
    if (name === '人民币') {
        return '10';
    }
    if (name === '港币') {
        return '21';
    }
    if (name === '美元') {
        return '32';
    }
    if (name === '欧元') {
        return '35';
    }
    if (name === '日元') {
        return '65';
    }
    return '10';
}

function getUUID() {
    var s = [];
    var hexDigits = "0123456789abcdef";
    for (var i = 0; i < 36; i++) {
        s[i] = hexDigits.substr(Math.floor(Math.random() * 0x10), 1);
    }
    s[14] = "4";
    s[19] = hexDigits.substr((s[19] & 0x3) | 0x8, 1);
    s[8] = s[13] = s[18] = s[23] = "-";
    return s.join("").replace(/-/g, '');
}